Real MySQL 8.0 (2)

ordilov / 2022. 3. 4

MySQL 서버는 머리 역할을 담당하는 MySQL 엔진과 손발 역할을 담당하는 스토리지 엔진으로 구분합니다. 스토리지 엔진은 핸들러 API를 만족하면 구현해서 서버에 추가해서 사용할 수 있습니다.

MySQL 엔진은 클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서, 전처리기, 옵티마이저로 구성됩니다. 스토리지 엔진은 디스크 스토리지에 저장과 읽어오는 부분을 담당합니다. MySQL 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 동시에 여러 개 사용할 수 있습니다. 쿼리 실행기에서 데이터를 쓰거나 읽을 때 스토리지 엔진에 요청하는 것을 핸들러 요청이라고 합니다. 이 때 사용되는 API를 핸들러 API 라고 합니다.

MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 작동하며 크게 포그라운드와 백그라운드로 구분합니다. 실행중인 스레드의 정보는 performance_schema와 thread 테이블을 통해 확인할 수 있습니다.

포그라운드 스레드

프그라운드 스레드는 최소 MySQL 서버에 접속한 클라이언트의 수만큼 존재하며, 주로 사용자의 요청 쿼리 문장을 처리합니다. 작업을 마치고 커넥션을 종료하면 스레드는 스레드 캐시로 되돌아갑니다. 이미 스레드 캐시에 일정 개수 이상의 대기 스레드가 있다면 스레드를 종료시켜 일정 개수만 유지시킵니다. 포그라운드 스레드는 데이터를 데이터 버퍼나 캐시로부터 가져오며 없는 경우 디스크에서 불러옵니다. InnoDB의 경우 버퍼나 캐시까지만 포그라운드에서 처리하고 나머지는 백그라운드에서 처리합니다.

백그라운드 스레드

• 인서트 버퍼를 병합
• 로그를 디스크로 기록
• InnoDB 버퍼 풀의 데이터를 디스크에 기록
• 데이터를 버퍼로 읽기
• 잠금이나 데드락을 모니터링

이 중 가장 중요한 것은 로그 스레드와 쓰기 쓰레드 입니다. 데이터를 읽는 작업은 클라이언트에서 주로 처리되지만 쓰기는 아주 많은 작업이 백그라운드에서 진행되어 2~4 정도로 설정해야 합니다.

데이터의 쓰기 작업은 버퍼링 되어 처리될 수 있어서 일괄 처리 기능이 존재합니다.

일반적으로 인덱스라고 하면 테이블에 사용자가 생성해둔 B-Tree 인덱스를 의미합니다. 어댑티브 해시 인덱스는 사용자가 수동으로 생성하는 인덱스가 아니라 InnoDB에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성합니다. 사용자는 innodb_adaptive_hash_index 변수를 이용해 기능을 활성화시킬 수 있습니다.

B-Tree 인덱스를 이용하면 빨라진다고 해도 이는 상대적이며 어떤 일을 하느냐에 따라 속도가 느려질 수 있습니다. 어댑티브 해시 인덱스는 이런 검색 시간을 줄여주기 위해 자주 읽히는 데이터의 키 값을 해시 인덱스로 만들어줍니다. 이렇게 되면 루트 노드부터 리프 노드까지 찾아가는 비용이 없어집니다.

이런 어댑티브 해시 인덱스가 도움이 안될 때는 다음과 같습니다.

• 디스크 읽기가 많은 경우
• 특정 패턴의 쿼리가 많은 경우
• 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우

도움이 되는 경우는 다음과 같습니다.

• 디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우
• 동등 조건 검색(동등 비교, IN) 연산이 많은 경우
• 쿼리가 데이터 중 일부 데이터에만 집중되는 경우

또 다른 단점으로는 해시 인덱스도 당연히 메모리를 차지하고, 변경이나 삭제할 때 인덱스도 함께 삭제하는 작업이 필요합니다. 이럴 때 도움이 되는지 불필요한지 판단할 수 있는 방법은 서버의 상태 값을 확인하는 것입니다.

SHOW ENGINE INNODB STATUS\G

hash-searches 와 non-hash searches 의 비율과 메모리 공간, CPU 사용량을 종합해 효율적인지 판단합니다.

크게 글로벌 메모리 영역과 로컬 메모리 영역으로 나뉩니다. 글로벌 메모리 영역은 운영체제가 할당해주며 하나의 메모리 공간에서 모든 스레드에 의해 공유됩니다.

• 테이블 캐시
• InnoDB 버퍼 풀
• InnoDB 어댑티브 해시 인덱스
• InnoDB 리두 로그 버퍼

로컬 메모리 영역은 세션 메모리 영역이라고도 하며 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역입니다. 대표적으로 커넥션 버퍼와 정렬 버퍼가 있습니다. 로컬 메모리는 스레드별로 독립적으로 할당되며 공유되지 않습니다.

• 정렬 버퍼
• 조인 버퍼
• 네트워크 버퍼
• 바이너리 로그 캐시

플러그인으로 스토리지 엔진이나 검색어 파서, 인증등을 플러그인으로 구현되어 제공합니다.

컴포넌트

MySQL 8.0부터는 플러그인 아키텍처를 대체하기 위해 컴포넌트 아키텍처가 지원됩니다. 플러그인 아키텍처는 다음 문제가 있었습니다.

• 플러그인은 MySQL 서버와 인터페이스할 수 있고, 플러그인끼리는 통신이 불가능
• 플러그인이 MySQL 서버의 변수나 함수를 직접 호출해 위험
• 플러그인은 상호 의존 관계를 설정할 수 없어 초기화가 어려움

쿼리 실행 구조

쿼리 파서, 전처리기, 옵티마이저, 실행 엔진, 핸들러로 구성됩니다. 쿼리 파서에서 문장을 최소 단위의 어휘로 분리하고 기본적인 문법 오류를 찾습니다. 전처리기에서 쿼리 문장에 구조적인 문제(테이블, 칼럼, 함수 존재 여부)등을 찾습니다. 옵티마이저에서 쿼리 문장을 최적화합니다. 실행 엔진은 핸들러에게 요청하고 요청받은 결과로 출력을 만들어냅니다.

커뮤니티 에디션에서는 제공되지 않으며 엔터프라이즈 에디션과 페르코나 에서 지원합니다. 스레드 풀을 이용하면 요청을 처리하는 스레드의 개수를 줄여서 제한된 개수의 스레드 처리로 자원 소모를 줄입니다. 당연히 스레드 개수가 너무 적다면 쿼리 처리가 더 느려질 수 있습니다. 일반적인 방법은 스레드 개수와 CPU 코어의 개수를 맞추는 방법이 있습니다.

모든 스레드가 일을 처리 중인데 다음 작업을 받을 때까지 걸리는 시간이 제한 시간보다 길다면 스레드를 추가합니다.

MySQL에서 가장 많이 사용되는 스토리지 엔진으로 레코드 기반 잠금을 제공하고, 높은 동시성 처리가 가능합니다.

프라이머리 키값 순서대로 디스크에 저장되어 다른 인덱스들은 프라이머리의 키 값을 논리적 주소로 사용합니다. 장점으로 프라이머리 키를 이용한 레인지 스캔의 속도가 올라갑니다. MyISAM 엔진에서는 프라이머리 키도 그저 인덱스처럼 레코드의 주소 값을 가집니다.

MyISAM이나 MEMORY 테이블에서는 외래 키를 지원하지 않습니다. InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요합니다. 그리고 변경 시에 부모 테이블과 자식 테이블에 데이터가 있는지 확인하기 때문에 잠금이 전파되고 데드락이 발생할 수 있습니다.

또 수동으로 데이터를 적재하거나 스키마 변경등을 신경쓰지 않으면 실패할 수 있습니다. 긴급 조치 때문에 외래키에 대한 체크를 잠시 멈출 수 있습니다. 부가적인 체크가 없기 때문에 속도가 빨라지지만 테이블 간의 관계가 깨진 채로 유지해서는 안됩니다. 외래키 체크가 비활성화 되면 CASCADE 옵션 처럼 외래키 관계 작업도 무시하게 됩니다.

SET foreign_key_checks=OFF;

레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능으로 잠금을 사용하지 않는 일관된 읽기를 제공합니다. InnoDB에서는 언두 로그를 이용해 이 기능을 구현합니다.

MVCC 기술을 이용하면 잠금을 걸지 않고 읽기 작업을 수행할 수 있습니다. 격리 수준이 SERIALIZABLE이 아닌 수준일 때 읽기 작업은 다른 트랜잭션과 관계 없이 바로 실행됩니다.

데드락 감지를 위해 잠금 대기 목록을 그래프 형태로 관리합니다. 교착 상태에 빠진 경우 트랜잭션 중 하나를 강제 종료합니다. 어떤 트랜잭션을 종료할지 기준은 언두 로그 양을 비교해 더 적은 쪽을 롤백시킵니다.

데드락 감지는 동시 처리 스레드가 매우 많아지거나 잠금이 많아지면 느려지게 됩니다. 이때 잠금 목록을 검사할 때 잠금 목록이 저장된 리스트를 잠금하고 찾는데 느려지면 진행중인 작업을 대기해야 합니다. 이 때문에 데드락 감지를 멈추려면 innodb_deadlock_detect를 off로 설정하면 됩니다. 이럴때 데드락이 걸리면 무한정 대기하게 되는데 timeout을 통해 일정시간이 지나면 자동으로 요청을 실패시킬 수 있습니다. innodb_lock_wait_timeout 변수로 데드락 감지를 끈 상태라면 기본값인 50초보다 훨씬 낮게 설정하고 써야 합니다.

InnoDB 엔진에서 가장 핵심적인 부분으로 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간입니다. 쓰기 작업을 지연시켜 일괄작업을 처리하는 버퍼 역할도 같이 합니다. 데이터를 변경하는 쿼리는 데이터 파일의 이곳저곳에 위치한 레코드를 변경하므로 랜덤한 디스크 작업을 발생시킵니다. 버퍼 풀로 모아서 처리하면 랜덤한 디스크 작업의 횟수를 줄일 수 있습니다.

버퍼 풀은 페이지 크기의 조각으로 쪼개어 필요할 때 페이지를 읽어 각 조각에 저장합니다. 페이지 크기 조각을 관리하기 위해 LRU 리스트, 플러시 리스트, 프리 리스트라는 3개의 자료구조를 관리합니다. 프리 리스트는 InnoDB 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들의 목록입니다. 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우 사용됩니다.

LRU 리스트는 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 유지해서 디스크 읽기를 최소화하는 것이 목적입니다.

1. 필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사
2. 디스크에 필요한 데이터 페이지를 버퍼 풀에 적재하고, 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가
3. 버퍼 풀의 LRU 헤더 부분에 적재된 데이터 페이지가 실제로 읽히면 MRU 헤더 부분으로 이동
4. 버퍼 풀에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했는지에 따라 나이가 부여 오래된 페이지는 버퍼 풀에서 제거, 버퍼 풀의 데이터 페이지가 사용되면 나이가 초기화되고 MRU의 헤더 부분으로 이동
5. 필요한 데이터가 자주 접근됐다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가

플러시 리스트는 디스크로 동기화되지 ㅇ낳은 데이터를 가진 데이터 페이지를 변경 시점 기준의 페이지 목록을 관리합니다. 디스크에서 읽은 상태 그대로 변경이 없다면 관리되지 않지만 변경이 가해지면 플러시 리스트에 기록되고 디스크에 기록되야합니다.

버퍼 풀은 서버의 메모리가 허용하는 만큼 크게 설정할수록 쿼리의 성능이 빨라집니다. 버퍼 풀은 데이터 캐시와 쓰기 버퍼링의 용도가 있는데 버퍼 풀 메모리 공간이 늘어도 데이터 캐시 기능만 향상시킵니다. 쓰기 버퍼링 기능을 향상시키려면 버퍼 풀과 리두 로그의 관계를 이해해야 합니다.

버퍼 풀은 변경되지 않은 클린 페이지와 더티 페이지를 가집니다. 더티 페이지는 디스크와 상태가 다르기 때문에 언젠가는 디스크로 기록해야 합니다. 리두 로그는 고정 크기 파일을 연결해서 사용하는데 데이터 변경이 계속되면 새로운 로그 엔트리로 덮어 쓰입니다. 따라서 재사용 가능한 공간과 재사용 불가능한 공간을 구분해서 관리합니다. 재사용 불가능한 공간을 활성 리두 로그라고 합니다.

이때 리두 로그 파일의 크기가 작다면 더티 페이지를 적게 보관할 수 밖에 없어 버퍼링 효과가 거의 없습니다. 반대로 리두 로그 파일의 크기가 크다면 한번에 기록해야 하는 더티 페이지가 너무 커지게 됩니다. 따라서 적정 수준으로 선택하는 것이 중요합니다.

트랜잭션과 격리 수준 보장을 위해 이전 버전의 데이터를 백업해놓는 것을 언두 로그라고 합니다. 언두 로그는 커밋된 값들이 백업되어 있는 것으로 변경된 데이터를 롤백하면 이 값을 불러와 복구합니다.

MySQL 5.5 이전 버전의 MySQL 서버에서는 한 번 증가한 언두 로그 공간은 다시 줄어들지 않았습니다. 예시로 100GB 짜리 테이블의 데이터를 삭제하면 그 기록이 그대로 언두 로그에 쌓이게 됩니다. 테이블에는 데이터가 전혀 없는 상태인데 말입니다.

뿐만 아니라 트랜잭션이 오래 지속되는 경우 언두 로그를 계속해서 보관하게 됩니다. 이렇게 늘어난 언두 로그 사용 공간을 서버를 새로 구축하지 않는 이상 줄일 수 없었는데 업그레이드 되면서 해결되었습니다.

설정 파일에서 log_error 라는 이름의 파라미터로 정의된 경로에 생성됩니다.

• 시작 과정, 관련 정보 에러 메시지
• 마지막으로 종료할 때 비정상적으로 종료된 경우 트랜잭션 복구 메시지
• 쿼리 처리 도중에 발생하는 문제 에러 메시지
• 비정상적 종료 커넥션 메시지
• 모니터링, 상태 조회 메시지
• 종료 메시지

long_query_time 시스템 변수에 설정한 시간 이상의 시간이 소요된 쿼리가 모두 기록됩니다. log_output 옵션을 이용해 슬로우 쿼리 로그를 파일로 기록할지 테이블로 기록할지 선택할 수 있습니다. 테이블로 선택한 경우 slow_log에 저장합니다.